﻿Imports System.Data.SqlClient
Public Class Compte
    Public Id As Integer
    Public user As String
    Public passe As String
    Public nom As String
    Public type As Boolean
    Public remarque As String
    Public listForm As List(Of CompteFrom)
    Public listverif As ArrayList
    Public ListCompte As List(Of Compte)


    'Les constructeur ------------------------

    Sub New(ByVal Id As Integer, ByVal User As String, ByVal passe As String, ByVal Nom As String, ByVal Type As Boolean, ByVal remarque As String)
        Me.Id = Id
        Me.user = User
        Me.nom = Nom
        Me.passe = passe
        Me.type = Type
        Me.remarque = remarque
    End Sub

    Sub New()

    End Sub

    'les listes ------------------------------


    Public List_registre As New ArrayList
    Public List_Reference As List(Of Reference)
    Public List_avocat_bureau As New ArrayList
    Public List_client As New ArrayList
    Public List_enemi As New ArrayList
    Public List_khabir As New ArrayList
    Public List_mofawad As New ArrayList
    Public List_avoca_partenaire As New ArrayList
    Public List_avocat_enemie As New ArrayList
    Public List_mokhabara As New ArrayList
    Public List_temoins As New ArrayList
    Public List_annuaire As New ArrayList
    Public List_employe As New ArrayList
    Public List_rendez_vous As New ArrayList
    Public lstRendezVousStruct As List(Of Partager.rendezVousStruct)
    Public List_proprietaire As New ArrayList
    Public List_info_fichier As New ArrayList
    Public List_Plainte As New ArrayList
    Public list_envoi As New List(Of Envoi)
    'les fonctions ------------------------

    Public Sub remplir_List_registre()
        Me.List_registre.Clear()
        Dim cmd As New SqlClient.SqlCommand("select * from Registre", Partager.con)
        Partager.open()
        Partager.dr = cmd.ExecuteReader
        While Partager.dr.Read
            Dim khab As New Registre(Partager.dr("id"), Partager.dr("Id_Compte"), Partager.dr("Date_Entrer"), Partager.dr("Date_sortir"))

            Me.List_registre.Add(khab)
        End While
        Partager.close()
    End Sub



    Public Sub Ajouter_registre(ByVal reg As Registre)


        Dim cmd As New SqlClient.SqlCommand("insert into Registre values (@Id_Compte,@Date_Entrer,@Date_sortir,GETDATE())", Partager.con)

        cmd.Parameters.Add("@Id_Compte", SqlDbType.VarChar).Value = Me.Id
        cmd.Parameters.Add("@Date_Entrer", SqlDbType.DateTime).Value = reg.date_entree
        cmd.Parameters.Add("@Date_sortir", SqlDbType.DateTime).Value = reg.date_sortie

        Partager.open()
        cmd.ExecuteNonQuery()
        Partager.close()

        Me.remplir_List_registre()
    End Sub



    'Public Sub remplirMesForm()
    '    Dim cmd As New SqlClient.SqlCommand("select  cf.Id_Form, cf.isRead, legende from Compte,Compte_Form cf,Form where" _
    '                                        & " cf.Id_Compte = Compte.id and cf.Id_Form = Form.id And cf.Id_Compte =" & Me.Id, Partager.con)
    '    Partager.open()
    '    Partager.dr = cmd.ExecuteReader

    '    If Partager.dr.HasRows Then
    '        Me.listForm = New List(Of Partager.mesForm)
    '        While Partager.dr.Read
    '            Dim t As New Partager.mesForm
    '            t.idForm = Partager.dr("Id_Form")
    '            t.legende = Partager.dr("legende")
    '            t.isRead = Partager.dr("isRead")
    '            Me.listForm.Add(t)
    '        End While
    '    End If
    '    Partager.close()
    'End Sub

    Public Sub remplirCompteEmplo()
        Me.ListCompte = New List(Of Compte)
        Dim cmd As New SqlClient.SqlCommand("select * from Compte where typee = 0", Partager.con)
        Partager.open()
        Partager.dr = cmd.ExecuteReader

        If Partager.dr.HasRows Then
            While Partager.dr.Read

                Partager.utilist = New Compte(Partager.dr("Id"), Partager.dr("Useer"), Partager.dr("Passe"), _
                                          Partager.dr("Nom_Complet"), Partager.dr("typee"), Partager.dr("Remarque"))
                Me.ListCompte.Add(Partager.utilist)
            End While
        End If
        Partager.dr.Close()
        For Each cmpt In Me.ListCompte
            cmpt.remplirLstcmptFrm()
        Next
        Partager.close()
    End Sub

    Public Sub remplirLstcmptFrm()
        Dim cmd As New SqlClient.SqlCommand("SELECT [Id_Compte],[Id_Form],[isRead] " & _
                                            "FROM [Gestion_Cabinet_Avovat].[dbo].[Compte_Form] " & _
                                            "where Id_Compte = " & Me.Id, Partager.con)
        Partager.open()
        Partager.dr = cmd.ExecuteReader
        Me.listForm = New List(Of CompteFrom)
        If Partager.dr.HasRows Then
            While Partager.dr.Read
                Dim khab As New CompteFrom(Partager.dr.GetInt32(0), Partager.dr.GetInt32(1), Partager.dr.GetBoolean(2))
                Me.listForm.Add(khab)
            End While
        End If
        Partager.dr.Close()
        Partager.close()
    End Sub

    Public Sub suprimmercmptFrm(ByVal idFrm As Int32)
        Dim cmd As New SqlClient.SqlCommand("delete from Compte_Form where Id_Compte=" & Me.Id _
                                        & " and Id_Form =" & idFrm, Partager.con)
        Partager.open()
        cmd.ExecuteNonQuery()
        Partager.close()

        Me.remplirLstcmptFrm()
    End Sub

    Public Sub Ajouter_droit(ByVal cmtFrm As CompteFrom)
        Dim str As String = "0"

        If cmtFrm.isRead Then
            str = "1"
        End If
        Dim cmd As New SqlClient.SqlCommand("insert into Compte_Form values(" & _
         cmtFrm.Id_Compte & "," & cmtFrm.Id_Form & "," & str & ",GETDATE())", Partager.con)

        Partager.open()
        cmd.ExecuteNonQuery()
        Partager.close()

        Me.remplirLstcmptFrm()
    End Sub

    '------------------------
    Public Sub remplir_List_avocat_enemie()
        Partager.close()
        Me.List_avocat_enemie.Clear()
        Dim cmd As New SqlClient.SqlCommand("select * from Avocat_Adversaire", Partager.con)
        Partager.open()
        Partager.dr = cmd.ExecuteReader
        Dim i As Integer = 0
        Dim nbrchamp As Integer = Partager.dr.FieldCount
        Dim khab As Avocat_enemie
        While Partager.dr.Read
            listverif = New ArrayList
            For i = 0 To nbrchamp - 1
                If Partager.dr(i) Is DBNull.Value Then
                    listverif.Add(Nothing)
                Else
                    listverif.Add(Partager.dr(i))
                End If
            Next
            khab = New Avocat_enemie(listverif)
            'khab.suprimer_phone(khab.Id)
            'khab.remplir_List_list_phone()
            Me.List_avocat_enemie.Add(khab)
        End While
        Partager.close()
    End Sub

    Public Sub suprimmer_avocat_enemie(ByVal id As Integer)

        Dim cmd As New SqlClient.SqlCommand("delete from Avocat_Adversaire where id=" & id, Partager.con)

        Partager.open()
        cmd.ExecuteNonQuery()
        Partager.close()

        Me.remplir_List_avocat_enemie()


    End Sub

    Public Sub Ajouter_avocat_enemie(ByVal ave As Avocat_enemie)

        Dim cmd As New SqlClient.SqlCommand("insert into Avocat_Adversaire values (@Num_Prof,@Nom,@Prenom,@Nom_F,@Prenom_F,@Ville,@Autorite,@Adresse,@Email,@Remarque,GETDATE())", Partager.con)

        cmd.Parameters.Add("@Num_Prof", SqlDbType.VarChar).Value = ave.Num_prof
        cmd.Parameters.Add("@Nom_F", SqlDbType.VarChar).Value = ave.Nom
        cmd.Parameters.Add("@Prenom_F", SqlDbType.VarChar).Value = ave.Prenom
        cmd.Parameters.Add("@Nom", SqlDbType.VarChar).Value = ave.Nom_a
        cmd.Parameters.Add("@Prenom", SqlDbType.VarChar).Value = ave.Prenom_a
        cmd.Parameters.Add("@Ville", SqlDbType.VarChar).Value = ave.Ville
        cmd.Parameters.Add("@Autorite", SqlDbType.VarChar).Value = ave.Autorite
        cmd.Parameters.Add("@Adresse", SqlDbType.VarChar).Value = ave.Adress
        cmd.Parameters.Add("@Email", SqlDbType.VarChar).Value = ave.Email

        cmd.Parameters.Add("@Remarque", SqlDbType.VarChar).Value = ave.Remarque

        Partager.open()
        cmd.ExecuteNonQuery()
        Partager.close()

        Me.remplir_List_avocat_enemie()


    End Sub

    Public Sub modifier_avocat_enemie(ByVal ave As Avocat_enemie, ByVal id As Integer)

        Dim cmd As New SqlClient.SqlCommand("update Avocat_Adversaire set  Num_Prof=@Num_Prof,Nom_F=@Nom_F,Prenom_F=@Prenom_F,Nom=@Nom,Prenom=@Prenom,Ville=@Ville,Autorite=@Autorite,Adresse=@Adresse,Email=@Email,Remarque=@Remarque where id=" & id, Partager.con)

        cmd.Parameters.Add("@Num_Prof", SqlDbType.VarChar).Value = ave.Num_prof
        cmd.Parameters.Add("@Nom_F", SqlDbType.VarChar).Value = ave.Nom
        cmd.Parameters.Add("@Prenom_F", SqlDbType.VarChar).Value = ave.Prenom
        cmd.Parameters.Add("@Nom", SqlDbType.VarChar).Value = ave.Nom_a
        cmd.Parameters.Add("@Prenom", SqlDbType.VarChar).Value = ave.Prenom_a
        cmd.Parameters.Add("@Ville", SqlDbType.VarChar).Value = ave.Ville

        cmd.Parameters.Add("@Autorite", SqlDbType.VarChar).Value = ave.Autorite
        cmd.Parameters.Add("@Adresse", SqlDbType.VarChar).Value = ave.Adress
        cmd.Parameters.Add("@Email", SqlDbType.VarChar).Value = ave.Email
        cmd.Parameters.Add("@Remarque", SqlDbType.VarChar).Value = ave.Remarque

        Partager.open()
        cmd.ExecuteNonQuery()
        Partager.close()

        Me.remplir_List_avocat_enemie()

    End Sub
    '------------------------

    Public Sub remplir_List_avocat_bureau()
        Partager.close()
        List_avocat_bureau.Clear()
        Dim cmd As New SqlClient.SqlCommand("select * from Avocat_Bureau", Partager.con)
        Partager.open()
        Partager.dr = cmd.ExecuteReader
        Dim i As Integer = 0
        Dim nbrchamp As Integer = Partager.dr.FieldCount
        Dim khab As Avocat_bureau
        While Partager.dr.Read
            listverif = New ArrayList
            For i = 0 To nbrchamp - 1
                If Partager.dr(i) Is DBNull.Value Then
                    listverif.Add(Nothing)
                Else
                    listverif.Add(Partager.dr(i))
                End If
            Next
            khab = New Avocat_bureau(listverif)
            'khab.suprimer_phone(khab.Id)
            'khab.remplir_List_list_phone()
            List_avocat_bureau.Add(khab)
        End While
        Partager.close()
    End Sub

    Public Sub suprimmer_avocat_bureau(ByVal id As Integer)

        Dim cmd As New SqlClient.SqlCommand("delete from Avocat_Bureau where id= " & id, Partager.con)

        Partager.open()
        cmd.ExecuteNonQuery()
        Partager.close()

        Me.remplir_List_avocat_bureau()


    End Sub

    Public Sub Ajouter_avocat_bureau(ByVal av As Avocat_bureau)
        Partager.close()
        Dim cmd As New SqlClient.SqlCommand("insert into Avocat_Bureau values (@Nom,@Prenom,@Nom_F,@Prenom_F,@Cin,@Date_Naissance,@Date_Employement,@Ville,@Situation_Social,@Num_Prof,@Ocupation,@Autorite,@salaire,@Email,@Adresse,@Actif,@Remarque,@Photo,@type,@majliss,GETDATE())", Partager.con)
        cmd.Parameters.Add("@Nom", SqlDbType.VarChar).Value = av.Nom_a
        cmd.Parameters.Add("@Prenom", SqlDbType.VarChar).Value = av.Prenom_a
        cmd.Parameters.Add("@Nom_F", SqlDbType.VarChar).Value = av.Nom
        cmd.Parameters.Add("@Prenom_F", SqlDbType.VarChar).Value = av.Prenom

        If av.type = "صاحب المكتب" Then
            cmd.Parameters.Add("@Cin", SqlDbType.VarChar).Value = DBNull.Value
            cmd.Parameters.Add("@Date_Naissance", SqlDbType.Date).Value = DBNull.Value
            cmd.Parameters.Add("@Date_Employement", SqlDbType.Date).Value = DBNull.Value

            cmd.Parameters.Add("@Situation_Social", SqlDbType.VarChar).Value = DBNull.Value
            cmd.Parameters.Add("@Num_Prof", SqlDbType.VarChar).Value = DBNull.Value
            cmd.Parameters.Add("@Ocupation", SqlDbType.VarChar).Value = DBNull.Value
            cmd.Parameters.Add("@salaire", SqlDbType.Float).Value = DBNull.Value
            cmd.Parameters.Add("@Actif", SqlDbType.Bit).Value = DBNull.Value
            cmd.Parameters.Add("@Remarque", SqlDbType.VarChar).Value = DBNull.Value
            cmd.Parameters.Add("@majliss", SqlDbType.Bit).Value = av.majlissA3la
        Else
            cmd.Parameters.Add("@Cin", SqlDbType.VarChar).Value = av.Cin
            cmd.Parameters.Add("@Date_Naissance", SqlDbType.Date).Value = av.Date_naiss
            cmd.Parameters.Add("@Date_Employement", SqlDbType.Date).Value = av.Date_employement

            cmd.Parameters.Add("@Situation_Social", SqlDbType.VarChar).Value = av.Situation_social
            cmd.Parameters.Add("@Num_Prof", SqlDbType.VarChar).Value = av.Num_prof
            cmd.Parameters.Add("@Ocupation", SqlDbType.VarChar).Value = av.Type_avocat
            cmd.Parameters.Add("@salaire", SqlDbType.Float).Value = av.Salaire
            cmd.Parameters.Add("@Actif", SqlDbType.Bit).Value = av.Actif
            cmd.Parameters.Add("@Remarque", SqlDbType.VarChar).Value = av.Remarque
            cmd.Parameters.Add("@majliss", SqlDbType.Bit).Value = DBNull.Value
        End If


        cmd.Parameters.Add("@Autorite", SqlDbType.VarChar).Value = av.Autorite
        cmd.Parameters.Add("@Ville", SqlDbType.VarChar).Value = av.Ville
        cmd.Parameters.Add("@Email", SqlDbType.VarChar).Value = av.Email
        cmd.Parameters.Add("@Adresse", SqlDbType.VarChar).Value = av.Adress

        cmd.Parameters.Add("@Photo", SqlDbType.VarBinary).Value = av.Photo
        cmd.Parameters.Add("@type", SqlDbType.VarChar).Value = av.type

        Partager.open()
        cmd.ExecuteNonQuery()
        Partager.close()
        remplir_List_avocat_bureau()
    End Sub

    Public Sub modifier_avocat_bureau(ByVal id As Integer, ByVal av As Avocat_bureau)
        Partager.close()
        Dim cmd As New SqlClient.SqlCommand("update  Avocat_Bureau set  Nom=@Nom,Prenom=@Prenom,Nom_F=@Nom_F,Prenom_F=@Prenom_F,Cin=@Cin,Date_Naissance=@Date_Naissance,Date_Employement=@Date_Employement,Ville=@Ville,Situation_Social=@Situation_Social,Num_Prof=@Num_Prof,Ocupation=@Ocupation,Autorite=@Autorite,salaire=@salaire,Email=@Email,Adresse=@Adresse,Actif=@Actif,Remarque=@Remarque,Photo=@Photo,type=@type,majlissA3la=@majliss where id =" & id, Partager.con)

        cmd.Parameters.Add("@Nom", SqlDbType.VarChar).Value = av.Nom_a
        cmd.Parameters.Add("@Prenom", SqlDbType.VarChar).Value = av.Prenom_a
        cmd.Parameters.Add("@Nom_F", SqlDbType.VarChar).Value = av.Nom
        cmd.Parameters.Add("@Prenom_F", SqlDbType.VarChar).Value = av.Prenom
        If av.type = "صاحب المكتب" Then
            cmd.Parameters.Add("@Cin", SqlDbType.VarChar).Value = DBNull.Value
            cmd.Parameters.Add("@Date_Naissance", SqlDbType.Date).Value = DBNull.Value
            cmd.Parameters.Add("@Date_Employement", SqlDbType.Date).Value = DBNull.Value

            cmd.Parameters.Add("@Situation_Social", SqlDbType.VarChar).Value = DBNull.Value
            cmd.Parameters.Add("@Num_Prof", SqlDbType.VarChar).Value = DBNull.Value
            cmd.Parameters.Add("@Ocupation", SqlDbType.VarChar).Value = DBNull.Value
            cmd.Parameters.Add("@salaire", SqlDbType.Float).Value = DBNull.Value
            cmd.Parameters.Add("@Actif", SqlDbType.Bit).Value = DBNull.Value
            cmd.Parameters.Add("@Remarque", SqlDbType.VarChar).Value = DBNull.Value
            cmd.Parameters.Add("@majliss", SqlDbType.Bit).Value = av.majlissA3la
        Else
            cmd.Parameters.Add("@Cin", SqlDbType.VarChar).Value = av.Cin
            cmd.Parameters.Add("@Date_Naissance", SqlDbType.Date).Value = av.Date_naiss
            cmd.Parameters.Add("@Date_Employement", SqlDbType.Date).Value = av.Date_employement

            cmd.Parameters.Add("@Situation_Social", SqlDbType.VarChar).Value = av.Situation_social
            cmd.Parameters.Add("@Num_Prof", SqlDbType.VarChar).Value = av.Num_prof
            cmd.Parameters.Add("@Ocupation", SqlDbType.VarChar).Value = av.Type_avocat
            cmd.Parameters.Add("@salaire", SqlDbType.Float).Value = av.Salaire
            cmd.Parameters.Add("@Actif", SqlDbType.Bit).Value = av.Actif
            cmd.Parameters.Add("@Remarque", SqlDbType.VarChar).Value = av.Remarque
            cmd.Parameters.Add("@majliss", SqlDbType.Bit).Value = DBNull.Value
        End If


        cmd.Parameters.Add("@Autorite", SqlDbType.VarChar).Value = av.Autorite
        cmd.Parameters.Add("@Ville", SqlDbType.VarChar).Value = av.Ville
        cmd.Parameters.Add("@Email", SqlDbType.VarChar).Value = av.Email
        cmd.Parameters.Add("@Adresse", SqlDbType.VarChar).Value = av.Adress

        cmd.Parameters.Add("@Photo", SqlDbType.VarBinary).Value = av.Photo
        cmd.Parameters.Add("@type", SqlDbType.VarChar).Value = av.type


        Partager.open()
        cmd.ExecuteNonQuery()
        Partager.close()

        Me.remplir_List_avocat_bureau()

    End Sub

    Public Sub remplir_List_client()
        Partager.close()
        Me.List_client.Clear()
        Dim cmd As New SqlClient.SqlCommand("select * from clients ", Partager.con)
        Partager.open()
        Partager.dr = cmd.ExecuteReader
        Dim i As Integer = 0
        Dim nbrchamp As Integer = Partager.dr.FieldCount
        Dim khab As Client
        While Partager.dr.Read
            listverif = New ArrayList
            For i = 0 To nbrchamp - 1
                If Partager.dr(i) Is DBNull.Value Then
                    listverif.Add(Nothing)
                Else
                    listverif.Add(Partager.dr(i))
                End If
            Next
            khab = New Client(listverif)
            'khab.remplir_List_list_phone()
            'khab.remplir_List_montant()
            List_client.Add(khab)
        End While
        Partager.dr.Close()
        Partager.close()

    End Sub

    Public Sub suprimmer_client(ByVal id As Integer)

        Dim cmd As New SqlClient.SqlCommand("delete from clients where id=" & id, Partager.con)

        Partager.open()
        cmd.ExecuteNonQuery()
        Partager.close()

        Me.remplir_List_client()


    End Sub

    Public Sub Ajouter_client(ByVal clt As Client)
        Dim cmd As New SqlClient.SqlCommand("insert into Clients values (@Nom,@Prenom,@Cin,@Adresse,@Email,@Nom_entreprise,@Representant_legal,@Occupation,@Ville,@Type_Client,@Remarque,@Photo,GETDATE())", Partager.con)
        cmd.Parameters.Add("@Nom", SqlDbType.VarChar).Value = clt.Nom
        cmd.Parameters.Add("@Prenom", SqlDbType.VarChar).Value = clt.Prenom
        cmd.Parameters.Add("@Cin", SqlDbType.VarChar).Value = clt.Cin
        cmd.Parameters.Add("@Adresse", SqlDbType.VarChar).Value = clt.Adress
        cmd.Parameters.Add("@Email", SqlDbType.VarChar).Value = clt.Email
        cmd.Parameters.Add("@Nom_entreprise", SqlDbType.VarChar).Value = clt.Nom_ent
        cmd.Parameters.Add("@Representant_legal", SqlDbType.VarChar).Value = clt.Representant_legal
        cmd.Parameters.Add("@Occupation", SqlDbType.VarChar).Value = clt.Occupation
        cmd.Parameters.Add("@Ville", SqlDbType.VarChar).Value = clt.Ville
        cmd.Parameters.Add("@Type_Client", SqlDbType.VarChar).Value = clt.Type
        cmd.Parameters.Add("@Remarque", SqlDbType.VarChar).Value = clt.Remarque
        cmd.Parameters.Add("@Photo", SqlDbType.VarBinary).Value = clt.photo
        Partager.open()
        cmd.ExecuteNonQuery()
        Partager.close()
        Me.remplir_List_client()
    End Sub

    Public Sub modifier_client(ByVal clt As Client, ByVal id As Integer)

        Dim cmd As New SqlClient.SqlCommand("update  Clients set Nom=@Nom,Prenom=@Prenom,Cin=@Cin,Adresse=@Adresse,Email=@Email,Nom_entreprise=@Nom_entreprise,Representant_legal=@Representant_legal,Occupation=@Occupation,Ville=@Ville,Type_Client=@Type_Client,Remarque=@Remarque,Photo=@Photo  where id=" & id, Partager.con)

        cmd.Parameters.Add("@Nom", SqlDbType.VarChar).Value = clt.Nom
        cmd.Parameters.Add("@Prenom", SqlDbType.VarChar).Value = clt.Prenom
        cmd.Parameters.Add("@Cin", SqlDbType.VarChar).Value = clt.Cin
        cmd.Parameters.Add("@Adresse", SqlDbType.VarChar).Value = clt.Adress
        cmd.Parameters.Add("@Email", SqlDbType.VarChar).Value = clt.Email
        cmd.Parameters.Add("@Nom_entreprise", SqlDbType.VarChar).Value = clt.Nom_ent
        cmd.Parameters.Add("@Representant_legal", SqlDbType.VarChar).Value = clt.Representant_legal
        cmd.Parameters.Add("@Occupation", SqlDbType.VarChar).Value = clt.Occupation
        cmd.Parameters.Add("@Ville", SqlDbType.VarChar).Value = clt.Ville
        cmd.Parameters.Add("@Type_Client", SqlDbType.VarChar).Value = clt.Type
        cmd.Parameters.Add("@Remarque", SqlDbType.VarChar).Value = clt.Remarque
        cmd.Parameters.Add("@Photo", SqlDbType.VarBinary).Value = clt.photo
        Partager.open()
        cmd.ExecuteNonQuery()
        Partager.close()

        Me.remplir_List_client()

    End Sub

    Public Sub remplir_List_enemi()
        Me.List_enemi.Clear()
        Dim cmd As New SqlClient.SqlCommand("select * from Adversaire", Partager.con)
        Partager.open()
        Partager.dr = cmd.ExecuteReader
        Dim i As Integer = 0
        Dim nbrchamp As Integer = Partager.dr.FieldCount
        Dim khab As Enemie
        While Partager.dr.Read
            listverif = New ArrayList
            For i = 0 To nbrchamp - 1
                If Partager.dr(i) Is DBNull.Value Then
                    listverif.Add(Nothing)
                Else
                    listverif.Add(Partager.dr(i))
                End If
            Next
            khab = New Enemie(listverif)
            'khab.remplir_List_list_phone()
            'khab.remplir_List_montant()
            List_enemi.Add(khab)
        End While
        Partager.close()
        Partager.dr.Close()
    End Sub

    Public Sub suprimmer_enemi(ByVal id As Integer)

        Dim cmd As New SqlClient.SqlCommand("delete from Adversaire where id= " & id, Partager.con)

        Partager.open()
        cmd.ExecuteNonQuery()
        Partager.close()

        Me.remplir_List_enemi()
    End Sub

    Public Sub Ajouter_enemi(ByVal enem As Enemie)

        Dim cmd As New SqlClient.SqlCommand("insert into Adversaire values (@Nom,@Prenom,@Cin,@Nom_entreprise,@Representant_legal,@AddRep,@Adresse,@Email,@Ville,@Type_Adversaire,@Remarque,GETDATE())", Partager.con)

        cmd.Parameters.Add("@Nom", SqlDbType.VarChar).Value = enem.Nom
        cmd.Parameters.Add("@Prenom", SqlDbType.VarChar).Value = enem.Prenom
        cmd.Parameters.Add("@Cin", SqlDbType.VarChar).Value = enem.Cin
        cmd.Parameters.Add("@Nom_entreprise", SqlDbType.VarChar).Value = enem.Nom_ent
        cmd.Parameters.Add("@Representant_legal", SqlDbType.VarChar).Value = enem.Representant_legal
        cmd.Parameters.Add("@AddRep", SqlDbType.NVarChar).Value = enem.AddRep
        cmd.Parameters.Add("@Adresse", SqlDbType.VarChar).Value = enem.Adress
        cmd.Parameters.Add("@Email", SqlDbType.VarChar).Value = enem.Email
        cmd.Parameters.Add("@Ville", SqlDbType.VarChar).Value = enem.Ville
        cmd.Parameters.Add("@Type_Adversaire", SqlDbType.VarChar).Value = enem.Type
        cmd.Parameters.Add("@Remarque", SqlDbType.VarChar).Value = enem.Remarque

        Partager.open()
        cmd.ExecuteNonQuery()
        Partager.close()

        Me.remplir_List_enemi()
    End Sub

    Public Sub modifier_enemi(ByVal enem As Enemie, ByVal id As Integer)

        Dim cmd As New SqlClient.SqlCommand("update Adversaire set Nom=@Nom,Prenom=@Prenom,Cin=@Cin,Nom_entreprise=@Nom_entreprise,Representant_legal=@Representant_legal,AdresseRepr=@AddRep,Adresse=@Adresse,Email=@Email,Ville=@Ville,Type_Adversaire=@Type_Adversaire,Remarque=@Remarque where id=" & id, Partager.con)

        cmd.Parameters.Add("@Nom", SqlDbType.VarChar).Value = enem.Nom
        cmd.Parameters.Add("@Prenom", SqlDbType.VarChar).Value = enem.Prenom
        cmd.Parameters.Add("@Cin", SqlDbType.VarChar).Value = enem.Cin
        cmd.Parameters.Add("@Nom_entreprise", SqlDbType.VarChar).Value = enem.Nom_ent
        cmd.Parameters.Add("@Representant_legal", SqlDbType.VarChar).Value = enem.Representant_legal
        cmd.Parameters.Add("@AddRep", SqlDbType.NVarChar).Value = enem.AddRep
        cmd.Parameters.Add("@Adresse", SqlDbType.VarChar).Value = enem.Adress
        cmd.Parameters.Add("@Email", SqlDbType.VarChar).Value = enem.Email
        cmd.Parameters.Add("@Ville", SqlDbType.VarChar).Value = enem.Ville
        cmd.Parameters.Add("@Type_Adversaire", SqlDbType.VarChar).Value = enem.Type
        cmd.Parameters.Add("@Remarque", SqlDbType.VarChar).Value = enem.Remarque

        Partager.open()
        cmd.ExecuteNonQuery()
        Partager.close()

        Me.remplir_List_enemi()

    End Sub



    Public Sub remplir_list_khabir()
        Me.List_khabir.Clear()
        Dim cmd As New SqlClient.SqlCommand("select * from  Khabir", Partager.con)
        Partager.open()
        Partager.dr = cmd.ExecuteReader
        Dim i As Integer = 0
        Dim nbrchamp As Integer = Partager.dr.FieldCount
        Dim khab As Khabir
        While Partager.dr.Read
            listverif = New ArrayList
            For i = 0 To nbrchamp - 1
                If Partager.dr(i) Is DBNull.Value Then
                    listverif.Add(Nothing)
                Else
                    listverif.Add(Partager.dr(i))
                End If
            Next
            khab = New Khabir(listverif)
            'khab.remplir_List_list_phone()
            'khab.remplir_List_montant()
            List_khabir.Add(khab)
        End While
        Partager.close()
    End Sub

    Public Sub suprimmer_khabir(ByVal id As Integer)

        Dim cmd As New SqlClient.SqlCommand("delete from Khabir where id =" & id, Partager.con)

        Partager.open()
        cmd.ExecuteNonQuery()
        Partager.close()

        Me.remplir_list_khabir()


    End Sub

    Public Sub Ajouter_khabir(ByVal khab As Khabir)

        Dim cmd As New SqlClient.SqlCommand("insert into Khabir values (@Nom,@Prenom,@Ville,@Adresse,@Remarque,GETDATE())", Partager.con)

        cmd.Parameters.Add("@Nom", SqlDbType.VarChar).Value = khab.Nom
        cmd.Parameters.Add("@Prenom", SqlDbType.VarChar).Value = khab.Prenom
        cmd.Parameters.Add("@Ville", SqlDbType.VarChar).Value = khab.Ville
        cmd.Parameters.Add("@Adresse", SqlDbType.VarChar).Value = khab.Adress
        cmd.Parameters.Add("@Remarque", SqlDbType.VarChar).Value = khab.Remarque


        Partager.open()
        cmd.ExecuteNonQuery()
        Partager.close()

        Me.remplir_list_khabir()


    End Sub

    Public Sub modifier_khabir(ByVal khab As Khabir, ByVal id As Integer)

        Dim cmd As New SqlClient.SqlCommand("update Khabir set Nom=@Nom,Prenom=@Prenom,Ville=@Ville,Adresse=@Adresse ,Remarque=@Remarque where id=" & id, Partager.con)

        cmd.Parameters.Add("@Nom", SqlDbType.VarChar).Value = khab.Nom
        cmd.Parameters.Add("@Prenom", SqlDbType.VarChar).Value = khab.Prenom
        cmd.Parameters.Add("@Ville", SqlDbType.VarChar).Value = khab.Ville
        cmd.Parameters.Add("@Adresse", SqlDbType.VarChar).Value = khab.Adress
        cmd.Parameters.Add("@Remarque", SqlDbType.VarChar).Value = khab.Remarque

        Partager.open()
        cmd.ExecuteNonQuery()
        Partager.close()

        Me.remplir_list_khabir()

    End Sub

    Public Sub remplir_List_avoca_partenaire()
        Me.List_avoca_partenaire.Clear()
        Dim cmd As New SqlClient.SqlCommand("select * from Avocat_Partonaire", Partager.con)
        Partager.open()
        Partager.dr = cmd.ExecuteReader
        Dim i As Integer = 0
        Dim nbrchamp As Integer = Partager.dr.FieldCount
        Dim khab As Avocat_partenaire
        While Partager.dr.Read
            listverif = New ArrayList
            For i = 0 To nbrchamp - 1
                If Partager.dr(i) Is DBNull.Value Then
                    listverif.Add(Nothing)
                Else
                    listverif.Add(Partager.dr(i))
                End If
            Next
            khab = New Avocat_partenaire(listverif)
            'khab.suprimer_phone(khab.Id)
            'khab.remplir_List_list_phone()
            Me.List_avoca_partenaire.Add(khab)
        End While

        Partager.close()
    End Sub

    Public Sub suprimmer_avoca_partenaire(ByVal id As Integer)

        Dim cmd As New SqlClient.SqlCommand("delete from Avocat_Partonaire where id =" & id, Partager.con)

        Partager.open()
        cmd.ExecuteNonQuery()
        Partager.close()

        Me.remplir_List_avoca_partenaire()


    End Sub

    Public Sub Ajouter_avoca_partenaire(ByVal avp As Avocat_partenaire)

        Dim cmd As New SqlClient.SqlCommand("insert into Avocat_Partonaire values (@Num_Prof,@Nom,@Prenom,@Nom_F,@Prenom_F,@Ville,@Autorite,@Email,@Adresse,@Remarque,GETDATE())", Partager.con)
        cmd.Parameters.Add("@Num_Prof", SqlDbType.VarChar).Value = avp.Num_Prof
        cmd.Parameters.Add("@Nom_F", SqlDbType.VarChar).Value = avp.Nom
        cmd.Parameters.Add("@Prenom_F", SqlDbType.VarChar).Value = avp.Prenom
        cmd.Parameters.Add("@Nom", SqlDbType.VarChar).Value = avp.Nom_a
        cmd.Parameters.Add("@Prenom", SqlDbType.VarChar).Value = avp.Prenom_a
        cmd.Parameters.Add("@Ville", SqlDbType.VarChar).Value = avp.Ville
        cmd.Parameters.Add("@Adresse", SqlDbType.VarChar).Value = avp.Adress
        cmd.Parameters.Add("@Autorite", SqlDbType.VarChar).Value = avp.Autorite
        cmd.Parameters.Add("@Email", SqlDbType.VarChar).Value = avp.Email
        cmd.Parameters.Add("@Remarque", SqlDbType.VarChar).Value = avp.Remarque

        Partager.open()
        cmd.ExecuteNonQuery()
        Partager.close()

        Me.remplir_List_avoca_partenaire()


    End Sub

    Public Sub modifier_avoca_partenaire(ByVal avp As Avocat_partenaire, ByVal i As Integer)

        Dim cmd As New SqlClient.SqlCommand("update  Avocat_Partonaire set Num_Prof=@Num_Prof,Nom=@Nom,Prenom_F=@Prenom_F,Nom_F=@Nom_F,Prenom=@Prenom,Ville=@Ville,Autorite=@Autorite,Email=@Email,Adresse=@Adresse,Remarque=@Remarque where id=" & i, Partager.con)


        cmd.Parameters.Add("@Num_Prof", SqlDbType.VarChar).Value = avp.Num_Prof
        cmd.Parameters.Add("@Nom_F", SqlDbType.VarChar).Value = avp.Nom
        cmd.Parameters.Add("@Prenom_F", SqlDbType.VarChar).Value = avp.Prenom
        cmd.Parameters.Add("@Nom", SqlDbType.VarChar).Value = avp.Nom_a
        cmd.Parameters.Add("@Prenom", SqlDbType.VarChar).Value = avp.Prenom_a
        cmd.Parameters.Add("@Ville", SqlDbType.VarChar).Value = avp.Ville
        cmd.Parameters.Add("@Adresse", SqlDbType.VarChar).Value = avp.Adress
        cmd.Parameters.Add("@Autorite", SqlDbType.VarChar).Value = avp.Autorite
        cmd.Parameters.Add("@Email", SqlDbType.VarChar).Value = avp.Email
        cmd.Parameters.Add("@Remarque", SqlDbType.VarChar).Value = avp.Remarque



        Partager.open()
        cmd.ExecuteNonQuery()
        Partager.close()

        Me.remplir_List_avoca_partenaire()

    End Sub


    Public Sub remplir_List_mokhabara()
        Me.List_mokhabara.Clear()
        Dim cmd As New SqlClient.SqlCommand("select * from Avocat_Mokhabara ", Partager.con)
        Partager.open()
        Partager.dr = cmd.ExecuteReader
        Dim i As Integer = 0
        Dim nbrchamp As Integer = Partager.dr.FieldCount
        Dim khab As Avocat_mokhabara
        If Partager.dr.HasRows Then
            While Partager.dr.Read
                listverif = New ArrayList
                For i = 0 To nbrchamp - 1
                    If Partager.dr(i) Is DBNull.Value Then
                        listverif.Add(Nothing)
                    Else
                        listverif.Add(Partager.dr(i))
                    End If
                Next
                khab = New Avocat_mokhabara(listverif)
                'khab.suprimer_phone(khab.Id)
                'khab.remplir_List_list_phone()
                Me.List_mokhabara.Add(khab)
            End While
        End If
        Partager.close()
    End Sub

    Public Sub suprimmer_mokhabara(ByVal id As Integer)

        Dim cmd As New SqlClient.SqlCommand("delete  from Avocat_Mokhabara where id =" & id, Partager.con)

        Partager.open()
        cmd.ExecuteNonQuery()
        Partager.close()

        Me.remplir_List_mokhabara()


    End Sub

    Public Sub Ajouter_mokhabara(ByVal avm As Avocat_mokhabara)

        Dim cmd As New SqlClient.SqlCommand("insert into Avocat_Mokhabara values (@Num_Prof,@Nom,@Prenom,@Nom_F,@Prenom_F,@Ville,@Autorite ,@Adresse,@Email ,@Remarque,GETDATE())", Partager.con)

        cmd.Parameters.Add("@Num_Prof", SqlDbType.VarChar).Value = avm.Num_Prof
        cmd.Parameters.Add("@Nom", SqlDbType.VarChar).Value = avm.Nom_a
        cmd.Parameters.Add("@Prenom", SqlDbType.VarChar).Value = avm.Prenom_a
        cmd.Parameters.Add("@Nom_F", SqlDbType.VarChar).Value = avm.Nom
        cmd.Parameters.Add("@Prenom_F", SqlDbType.VarChar).Value = avm.Prenom
        cmd.Parameters.Add("@Ville", SqlDbType.VarChar).Value = avm.Ville
        cmd.Parameters.Add("@Autorite", SqlDbType.VarChar).Value = avm.Autorite
        cmd.Parameters.Add("@Adresse", SqlDbType.VarChar).Value = avm.Adress
        cmd.Parameters.Add("@Email", SqlDbType.VarChar).Value = avm.Email
        cmd.Parameters.Add("@Remarque", SqlDbType.VarChar).Value = avm.Remarque


        Partager.open()
        cmd.ExecuteNonQuery()
        Partager.close()

        Me.remplir_List_mokhabara()


    End Sub

    Public Sub modifier_mokhabara(ByVal avm As Avocat_mokhabara, ByVal id As Integer)

        Dim cmd As New SqlClient.SqlCommand("update Avocat_Mokhabara set Num_Prof=@Num_Prof,Nom_F=@Nom_F,Prenom_F=@Prenom_F,Nom=@Nom,Prenom=@Prenom,Ville=@Ville,Autorite=@Autorite ,Adresse=@Adresse,Email=@Email,Remarque=@Remarque where id=" & id, Partager.con)

        cmd.Parameters.Add("@Num_Prof", SqlDbType.VarChar).Value = avm.Num_Prof
        cmd.Parameters.Add("@Nom", SqlDbType.VarChar).Value = avm.Nom_a
        cmd.Parameters.Add("@Prenom", SqlDbType.VarChar).Value = avm.Prenom_a
        cmd.Parameters.Add("@Nom_F", SqlDbType.VarChar).Value = avm.Nom
        cmd.Parameters.Add("@Prenom_F", SqlDbType.VarChar).Value = avm.Prenom
        cmd.Parameters.Add("@Ville", SqlDbType.VarChar).Value = avm.Ville
        cmd.Parameters.Add("@Autorite", SqlDbType.VarChar).Value = avm.Autorite
        cmd.Parameters.Add("@Adresse", SqlDbType.VarChar).Value = avm.Adress
        cmd.Parameters.Add("@Email", SqlDbType.VarChar).Value = avm.Email
        cmd.Parameters.Add("@Remarque", SqlDbType.VarChar).Value = avm.Remarque

        Partager.open()
        cmd.ExecuteNonQuery()
        Partager.close()

        Me.remplir_List_mokhabara()

    End Sub



    Public Sub remplir_List_temoins()
        Partager.close()
        Me.List_temoins.Clear()
        Dim cmd As New SqlClient.SqlCommand("select * from temoins ", Partager.con)
        Partager.open()
        Partager.dr = cmd.ExecuteReader
        Dim i As Integer = 0
        Dim nbrchamp As Integer = Partager.dr.FieldCount
        Dim khab As Temoins
        While Partager.dr.Read
            listverif = New ArrayList
            For i = 0 To nbrchamp - 1
                If Partager.dr(i) Is DBNull.Value Then
                    listverif.Add(Nothing)
                Else
                    listverif.Add(Partager.dr(i))
                End If
            Next
            khab = New Temoins(listverif)
            'khab.suprimer_phone(khab.Id)
            'khab.remplir_List_list_phone()
            Me.List_temoins.Add(khab)
        End While
        Partager.close()
    End Sub

    Public Sub suprimmer_temoins(ByVal id As Integer)

        Dim cmd As New SqlClient.SqlCommand("delete from temoins where id=" & id, Partager.con)

        Partager.open()
        cmd.ExecuteNonQuery()
        Partager.close()

        Me.remplir_List_temoins()


    End Sub

    Public Sub Ajouter_temoins(ByVal tem As Temoins)

        Dim cmd As New SqlClient.SqlCommand("insert into temoins values (@Nom,@Prenom,@Cin,@Ville,@Adresse,@Remarque,GETDATE())", Partager.con)

        cmd.Parameters.Add("@Nom", SqlDbType.VarChar).Value = tem.Nom
        cmd.Parameters.Add("@Prenom", SqlDbType.VarChar).Value = tem.Prenom
        cmd.Parameters.Add("@Cin", SqlDbType.VarChar).Value = tem.Cin
        cmd.Parameters.Add("@Ville", SqlDbType.VarChar).Value = tem.Ville
        cmd.Parameters.Add("@Adresse", SqlDbType.VarChar).Value = tem.Adress
        cmd.Parameters.Add("@Remarque", SqlDbType.VarChar).Value = tem.Remarque


        Partager.open()
        cmd.ExecuteNonQuery()
        Partager.close()

        Me.remplir_List_temoins()


    End Sub

    Public Sub modifier_temoins(ByVal tem As Temoins, ByVal id As Integer)
        Dim cmd As New SqlClient.SqlCommand("update  temoins set Nom=@Nom,Prenom=@Prenom,Cin=@Cin,Ville=@Ville,Adresse=@Adresse,Remarque=@Remarque where id=" & id, Partager.con)

        cmd.Parameters.Add("@Nom", SqlDbType.VarChar).Value = tem.Nom
        cmd.Parameters.Add("@Prenom", SqlDbType.VarChar).Value = tem.Prenom
        cmd.Parameters.Add("@Cin", SqlDbType.VarChar).Value = tem.Cin
        cmd.Parameters.Add("@Ville", SqlDbType.VarChar).Value = tem.Ville
        cmd.Parameters.Add("@Adresse", SqlDbType.VarChar).Value = tem.Adress
        cmd.Parameters.Add("@Remarque", SqlDbType.VarChar).Value = tem.Remarque

        Partager.open()
        cmd.ExecuteNonQuery()
        Partager.close()

        Me.remplir_List_temoins()

    End Sub



    Public Sub remplir_List_annuaire()
        Me.List_annuaire.Clear()
        Dim cmd As New SqlClient.SqlCommand("select * from Annuaire", Partager.con)
        Partager.open()
        Partager.dr = cmd.ExecuteReader
        Dim i As Integer = 0
        Dim nbrchamp As Integer = Partager.dr.FieldCount
        Dim khab As Annuaire
        While Partager.dr.Read
            listverif = New ArrayList
            For i = 0 To nbrchamp - 1
                If Partager.dr(i) Is DBNull.Value Then
                    listverif.Add(Nothing)
                Else
                    listverif.Add(Partager.dr(i))
                End If
            Next
            khab = New Annuaire(listverif)
            'khab.suprimer_phone(khab.Id)
            'khab.remplir_List_list_phone()
            Me.List_annuaire.Add(khab)
        End While
        Partager.close()
    End Sub

    Public Sub suprimmer_annuaire(ByVal id As Integer)

        Dim cmd As New SqlClient.SqlCommand("delete from Annuaire where id=" & id, Partager.con)

        Partager.open()
        cmd.ExecuteNonQuery()
        Partager.close()

        Me.remplir_List_annuaire()


    End Sub

    Public Sub Ajouter_annuaire(ByVal ann As Annuaire)

        Dim cmd As New SqlClient.SqlCommand("insert into Annuaire values (@nomComp,@Email,@Adresse,@Occupation,@Remarque,GETDATE())", Partager.con)

        cmd.Parameters.Add("@nomComp", SqlDbType.VarChar).Value = ann.nomComp
        cmd.Parameters.Add("@Email", SqlDbType.VarChar).Value = ann.email
        cmd.Parameters.Add("@Occupation", SqlDbType.VarChar).Value = ann.occupation
        cmd.Parameters.Add("@Adresse", SqlDbType.VarChar).Value = ann.Adress
        cmd.Parameters.Add("@Remarque", SqlDbType.VarChar).Value = ann.Remarque

        Partager.open()
        cmd.ExecuteNonQuery()
        Partager.close()

        Me.remplir_List_annuaire()


    End Sub

    Public Sub modifier_annuaire(ByVal ann As Annuaire, ByVal id As Integer)

        Dim cmd As New SqlClient.SqlCommand("update  Annuaire set nomComp=@nomComp,Email=@Email,Adresse=@Adresse,Occupation=@Occupation,Remarque=@Remarque where id=" & id, Partager.con)

        cmd.Parameters.Add("@Nom", SqlDbType.VarChar).Value = ann.nomComp
        cmd.Parameters.Add("@Email", SqlDbType.VarChar).Value = ann.email
        cmd.Parameters.Add("@Occupation", SqlDbType.VarChar).Value = ann.occupation
        cmd.Parameters.Add("@Adresse", SqlDbType.VarChar).Value = ann.Adress
        cmd.Parameters.Add("@Remarque", SqlDbType.VarChar).Value = ann.Remarque

        Partager.open()
        cmd.ExecuteNonQuery()
        Partager.close()

        Me.remplir_List_annuaire()

    End Sub



    Public Sub remplir_List_employe()
        Me.List_employe.Clear()
        Dim cmd As New SqlClient.SqlCommand("select * from Employes", Partager.con)
        Partager.open()
        Partager.dr = cmd.ExecuteReader
        Dim i As Integer = 0
        Dim nbrchamp As Integer = Partager.dr.FieldCount
        Dim khab As Employe
        While Partager.dr.Read
            listverif = New ArrayList
            For i = 0 To nbrchamp - 1
                If Partager.dr(i) Is DBNull.Value Then
                    listverif.Add(Nothing)
                Else
                    listverif.Add(Partager.dr(i))
                End If
            Next
            khab = New Employe(listverif)
            'khab.suprimer_phone(khab.Id)
            'khab.remplir_List_list_phone()
            List_employe.Add(khab)
        End While
        Partager.close()
    End Sub

    Public Sub suprimmer_employe(ByVal id As Integer)

        Dim cmd As New SqlClient.SqlCommand("delete from Employes where id=" & id, Partager.con)

        Partager.open()
        cmd.ExecuteNonQuery()
        Partager.close()

        Me.remplir_List_employe()


    End Sub

    Public Sub Ajouter_employe(ByVal emp As Employe)

        Dim cmd As New SqlClient.SqlCommand("insert into Employes values (@Nom,@Prenom,@Nom_F,@Prenom_F,@Cin,@Date_Naissance,@Adresse,@Date_Emploi,@Situation_Social,@Occupation,@Ville,@Salaire,@Email,@Photo,@Actif,@Remarque,GETDATE())", Partager.con)

        cmd.Parameters.Add("@Nom", SqlDbType.VarChar).Value = emp.Nom_a
        cmd.Parameters.Add("@Prenom", SqlDbType.VarChar).Value = emp.Prenom_a
        cmd.Parameters.Add("@Nom_F", SqlDbType.VarChar).Value = emp.Nom
        cmd.Parameters.Add("@Prenom_F", SqlDbType.VarChar).Value = emp.Prenom
        cmd.Parameters.Add("@Cin", SqlDbType.VarChar).Value = emp.cin
        cmd.Parameters.Add("@Date_Naissance", SqlDbType.DateTime2).Value = emp.Date_naiss
        cmd.Parameters.Add("@Adresse", SqlDbType.VarChar).Value = emp.Adress
        cmd.Parameters.Add("@Date_Emploi", SqlDbType.DateTime2).Value = emp.Date_emploi
        cmd.Parameters.Add("@Situation_Social", SqlDbType.VarChar).Value = emp.Situation_social
        cmd.Parameters.Add("@Occupation", SqlDbType.VarChar).Value = emp.occupation
        cmd.Parameters.Add("@Ville", SqlDbType.VarChar).Value = emp.Ville
        cmd.Parameters.Add("@Salaire", SqlDbType.Float).Value = emp.salaire
        cmd.Parameters.Add("@Email", SqlDbType.VarChar).Value = emp.email
        cmd.Parameters.Add("@Photo", SqlDbType.VarBinary).Value = emp.photo
        cmd.Parameters.Add("@Actif", SqlDbType.Bit).Value = emp.Actif
        cmd.Parameters.Add("@Remarque", SqlDbType.VarChar).Value = emp.Remarque


        Partager.open()
        cmd.ExecuteNonQuery()
        Partager.close()

        Me.remplir_List_employe()


    End Sub

    Public Sub modifier_employe(ByVal emp As Employe, ByVal id As Integer)

        Dim cmd As New SqlClient.SqlCommand("update Employes set Nom=@Nom,Prenom=@Prenom,Nom_F=@Nom_F,Prenom_F=@Prenom_F,Cin=@Cin,Date_Naissance=@Date_Naissance,Adresse=@Adresse,Date_Emploi=@Date_Emploi,Situation_Social=@Situation_Social,Occupation=@Occupation,Ville=@Ville,Salaire=@Salaire,Email=@Email,Photo=@Photo,Actif=@Actif,Remarque=@Remarque where id=" & id, Partager.con)

        cmd.Parameters.Add("@Nom", SqlDbType.VarChar).Value = emp.Nom_a
        cmd.Parameters.Add("@Prenom", SqlDbType.VarChar).Value = emp.Prenom_a
        cmd.Parameters.Add("@Nom_F", SqlDbType.VarChar).Value = emp.Nom
        cmd.Parameters.Add("@Prenom_F", SqlDbType.VarChar).Value = emp.Prenom
        cmd.Parameters.Add("@Cin", SqlDbType.VarChar).Value = emp.cin
        cmd.Parameters.Add("@Date_Naissance", SqlDbType.DateTime2).Value = emp.Date_naiss
        cmd.Parameters.Add("@Adresse", SqlDbType.VarChar).Value = emp.Adress
        cmd.Parameters.Add("@Date_Emploi", SqlDbType.DateTime2).Value = emp.Date_emploi
        cmd.Parameters.Add("@Situation_Social", SqlDbType.VarChar).Value = emp.Situation_social
        cmd.Parameters.Add("@Occupation", SqlDbType.VarChar).Value = emp.occupation
        cmd.Parameters.Add("@Ville", SqlDbType.VarChar).Value = emp.Ville
        cmd.Parameters.Add("@Salaire", SqlDbType.Float).Value = emp.salaire
        cmd.Parameters.Add("@Email", SqlDbType.VarChar).Value = emp.email
        cmd.Parameters.Add("@Photo", SqlDbType.VarBinary).Value = emp.photo
        cmd.Parameters.Add("@Actif", SqlDbType.Bit).Value = emp.Actif
        cmd.Parameters.Add("@Remarque", SqlDbType.VarChar).Value = emp.Remarque

        Partager.open()
        cmd.ExecuteNonQuery()
        Partager.close()

        Me.remplir_List_employe()

    End Sub




    Public Sub remplir_List_rendez_vous()
        Me.List_rendez_vous.Clear()
        Dim cmd As New SqlClient.SqlCommand("select * from Rendez_Vous", Partager.con)
        Partager.open()
        Partager.dr = cmd.ExecuteReader
        Dim i As Integer = 0
        Dim nbrchamp As Integer = Partager.dr.FieldCount
        Dim khab As Rendez_vous
        While Partager.dr.Read
            listverif = New ArrayList
            For i = 0 To nbrchamp - 1
                If Partager.dr(i) Is DBNull.Value Then
                    listverif.Add(Nothing)
                Else
                    listverif.Add(Partager.dr(i))
                End If
            Next
            khab = New Rendez_vous(listverif)
            'khab.suprimer_phone(khab.Id)
            'khab.remplir_List_list_phone()
            List_rendez_vous.Add(khab)
        End While
        Partager.close()
    End Sub

    Public Sub suprimmer_rendez_vous(ByVal id As Integer)

        Dim cmd As New SqlClient.SqlCommand("delete from Rendez_Vous where id=" & id, Partager.con)

        Partager.open()
        cmd.ExecuteNonQuery()
        Partager.close()

        Me.remplir_List_rendez_vous()
        Partager.remplirRendezVous()

    End Sub

    Public Sub Ajouter_rendez_vous(ByVal rdv As Rendez_vous)

        Dim cmd As New SqlClient.SqlCommand("insert into Rendez_Vous values (@date,@Nom_Complet,@Cause,@Heur,@Remarque,GETDATE())", Partager.con)

        cmd.Parameters.Add("@date", SqlDbType.Date).Value = rdv.Date_rendez_vous
        cmd.Parameters.Add("@Nom_Complet", SqlDbType.VarChar).Value = rdv.Nom_complet
        cmd.Parameters.Add("@Cause", SqlDbType.Text).Value = rdv.Cause
        cmd.Parameters.Add("@Heur", SqlDbType.VarChar).Value = rdv.Heure
        cmd.Parameters.Add("@Remarque", SqlDbType.Text).Value = rdv.Remarque

        Partager.open()
        cmd.ExecuteNonQuery()
        Partager.close()

        Me.remplir_List_rendez_vous()
        Partager.remplirRendezVous()
        If Not Frm_Login.timSec.Enabled Then
            Frm_Login.timSec.Start()
        End If
        Partager.isRendezVousAdd = True
    End Sub

    Public Sub modifier_rendez_vous(ByVal rdv As Rendez_vous, ByVal id As Integer)

        Dim cmd As New SqlClient.SqlCommand("update  Rendez_Vous set date=@date,Nom_Complet=@Nom_Complet,Cause=@Cause,Heur=@Heur,Remarque=@Remarque where id =" & id, Partager.con)

        cmd.Parameters.Add("@date", SqlDbType.Date).Value = rdv.Date_rendez_vous
        cmd.Parameters.Add("@Nom_Complet", SqlDbType.VarChar).Value = rdv.Nom_complet
        cmd.Parameters.Add("@Cause", SqlDbType.VarChar).Value = rdv.Cause
        cmd.Parameters.Add("@Heur", SqlDbType.VarChar).Value = rdv.Heure
        cmd.Parameters.Add("@Remarque", SqlDbType.VarChar).Value = rdv.Remarque

        Partager.open()
        cmd.ExecuteNonQuery()
        Partager.close()

        Me.remplir_List_rendez_vous()
        Partager.remplirRendezVous()
        If Not Frm_Login.timSec.Enabled Then
            Frm_Login.timSec.Start()
        End If
        Partager.isRendezVousAdd = True
    End Sub




    'Public Sub remplir_List_mofawad()
    '    Me.List_mofawad.Clear()
    '    Dim cmd As New SqlClient.SqlCommand("select * from Mofawad", Partager.con)
    '    Partager.open()
    '    Partager.dr = cmd.ExecuteReader
    '    Dim i As Integer = 0
    '    Dim nbrchamp As Integer = Partager.dr.FieldCount
    '    Dim khab As Mofawid
    '    While Partager.dr.Read
    '        listverif = New ArrayList
    '        For i = 0 To nbrchamp - 1
    '            If Partager.dr(i) Is DBNull.Value Then
    '                listverif.Add(Nothing)
    '            Else
    '                listverif.Add(Partager.dr(i))
    '            End If
    '        Next
    '        khab = New Mofawid(listverif)
    '        'khab.suprimer_phone(khab.Id)
    '        'khab.remplir_List_list_phone()
    '        Me.List_mofawad.Add(khab)
    '    End While

    '    Partager.close()
    'End Sub

    'Public Sub suprimmer_mofawad(ByVal id As Integer)

    '    Dim cmd As New SqlClient.SqlCommand("delete from Mofawad where id=" & id, Partager.con)

    '    Partager.open()
    '    cmd.ExecuteNonQuery()
    '    Partager.close()

    '    Me.remplir_List_mofawad()


    'End Sub

    'Public Sub Ajouter_mofawad(ByVal prb As Mofawid)

    '    Dim cmd As New SqlClient.SqlCommand("insert into Mofawad values (@Nom,@Prenom,@Ville,@Adresse,@Email,@type,@Remarque,GETDATE())", Partager.con)

    '    cmd.Parameters.Add("@Nom", SqlDbType.VarChar).Value = prb.Nom
    '    cmd.Parameters.Add("@Prenom", SqlDbType.VarChar).Value = prb.Prenom
    '    cmd.Parameters.Add("@Ville", SqlDbType.VarChar).Value = prb.Ville
    '    cmd.Parameters.Add("@Adresse", SqlDbType.VarChar).Value = prb.Adress
    '    cmd.Parameters.Add("@Email", SqlDbType.VarChar).Value = prb.Email
    '    cmd.Parameters.Add("@type", SqlDbType.VarChar).Value = prb.type
    '    cmd.Parameters.Add("@Remarque", SqlDbType.VarChar).Value = prb.Remarque


    '    Partager.open()
    '    cmd.ExecuteNonQuery()
    '    Partager.close()

    '    Me.remplir_List_mofawad()


    'End Sub

    'Public Sub modifier_mofawad(ByVal prb As Mofawid, ByVal id As Integer)

    '    Dim cmd As New SqlClient.SqlCommand("update  Mofawad set Nom=@Nom,Prenom=@Prenom,Ville=@Ville,Adresse=@Adresse,Email=@Email,type=@type,Remarque=@Remarque where id=" & id, Partager.con)

    '    cmd.Parameters.Add("@Nom", SqlDbType.VarChar).Value = prb.Nom
    '    cmd.Parameters.Add("@Prenom", SqlDbType.VarChar).Value = prb.Prenom
    '    cmd.Parameters.Add("@Ville", SqlDbType.VarChar).Value = prb.Ville
    '    cmd.Parameters.Add("@Adresse", SqlDbType.VarChar).Value = prb.Adress
    '    cmd.Parameters.Add("@Email", SqlDbType.VarChar).Value = prb.Email
    '    cmd.Parameters.Add("@type", SqlDbType.VarChar).Value = prb.type
    '    cmd.Parameters.Add("@Remarque", SqlDbType.VarChar).Value = prb.Remarque

    '    Partager.open()
    '    cmd.ExecuteNonQuery()
    '    Partager.close()

    '    Me.remplir_List_mofawad()

    'End Sub


    Public Sub remplir_List_info_fichier()
        Me.List_info_fichier.Clear()
        Dim cmd As New SqlClient.SqlCommand("select * from Dossier", Partager.con)
        Dim str As String
        Partager.open()
        Partager.dr = cmd.ExecuteReader
        While Partager.dr.Read
            str = ""
            If Partager.dr("Date_Inscr_Tribunal") = "01/01/1900" Then
                str = ""
            Else
                str = Partager.dr("Date_Inscr_Tribunal")
            End If
            Dim khab As New Dossier(Partager.dr("id"), Partager.dr("Num_Dossier"), Partager.dr("Tribunal"), Partager.dr("Object"), Partager.dr("Grade"), _
            Partager.dr("Methode"), Partager.dr("Type_Appel"), str _
                                    , Partager.dr("Etat_Appel"), Partager.dr("Archive"), Partager.dr("Remarque"))

            khab.Ref = Partager.dr("Ref")
            List_info_fichier.Add(khab)
        End While

        Partager.close()
        Partager.dr.Close()

    End Sub

    Public Sub suprimmer_info_fichier(ByVal id As Integer)

        Dim cmd As New SqlClient.SqlCommand("delete from Dossier where id=" & id, Partager.con)

        Partager.open()
        cmd.ExecuteNonQuery()
        Partager.close()

        Me.remplir_List_info_fichier()


    End Sub

    Public Sub Ajouter_info_fichier(ByVal prb As Dossier)
        Dim cmd As New SqlClient.SqlCommand("insert into Dossier values (@Num_Dossier,-1,@Tribunal,@Object,@Grade,@Methode,@Type_Appel,'" _
                                            & prb.Date_inscr_tribunal & "',@Etat_Appel,@Remarque,0,GETDATE())", Partager.con)

        cmd.Parameters.Add("@Num_Dossier", SqlDbType.VarChar).Value = prb.Num
        cmd.Parameters.Add("@Tribunal", SqlDbType.VarChar).Value = prb.Tribunal
        cmd.Parameters.Add("@Object", SqlDbType.VarChar).Value = prb.Objet
        cmd.Parameters.Add("@Grade", SqlDbType.VarChar).Value = prb.Grade
        cmd.Parameters.Add("@Methode", SqlDbType.VarChar).Value = prb.Methode
        cmd.Parameters.Add("@Type_Appel", SqlDbType.VarChar).Value = prb.Type_appel
        cmd.Parameters.Add("@Etat_Appel", SqlDbType.VarChar).Value = prb.Etat_appel
        cmd.Parameters.Add("@Remarque", SqlDbType.VarChar).Value = prb.Remarque
        Partager.open()
        cmd.ExecuteNonQuery()
        Partager.close()

        Me.remplir_List_info_fichier()
    End Sub

    Public Sub modifier_info_fichier(ByVal prb As Dossier, ByVal id As Integer)



        Dim cmd As New SqlClient.SqlCommand("update  Dossier set Num_Dossier=@Num_Dossier,Tribunal=@Tribunal,Object=@Object,Grade=@Grade,Methode=@Methode,Type_Appel=@Type_Appel,Date_Inscr_Tribunal='" & prb.Date_inscr_tribunal & "',Etat_Appel=@Etat_Appel,Remarque=@Remarque,Archive=@Archive where id=" & id, Partager.con)

        cmd.Parameters.Add("@Num_Dossier", SqlDbType.VarChar).Value = prb.Num


        cmd.Parameters.Add("@Tribunal", SqlDbType.VarChar).Value = prb.Tribunal
        cmd.Parameters.Add("@Object", SqlDbType.VarChar).Value = prb.Objet
        cmd.Parameters.Add("@Grade", SqlDbType.VarChar).Value = prb.Grade
        cmd.Parameters.Add("@Methode", SqlDbType.VarChar).Value = prb.Methode
        cmd.Parameters.Add("@Type_Appel", SqlDbType.VarChar).Value = prb.Type_appel
        cmd.Parameters.Add("@Etat_Appel", SqlDbType.VarChar).Value = prb.Etat_appel
        cmd.Parameters.Add("@Remarque", SqlDbType.VarChar).Value = prb.Remarque
        cmd.Parameters.Add("@Archive", SqlDbType.VarChar).Value = prb.Archive

        Partager.open()
        cmd.ExecuteNonQuery()
        Partager.close()

        Me.remplir_List_info_fichier()

    End Sub

    Public Sub modifier_archive(ByVal fich As Dossier, ByVal str As String)
        Dim cmd As New SqlClient.SqlCommand("update  Dossier set Archive = " & str & " where id=" & fich.id, Partager.con)
        Partager.open()
        cmd.ExecuteNonQuery()
        Partager.close()
        Me.remplir_List_info_fichier()
    End Sub



    Public Sub ajouter_Ref(ByVal Refe As Reference)
        Dim cmd As New SqlClient.SqlCommand("insert into Reference values (@Ref,@dateInscriBur)", Partager.con)
        cmd.Parameters.Add("@Ref", SqlDbType.Int).Value = Refe.Ref
        cmd.Parameters.Add("@dateInscriBur", SqlDbType.Date).Value = Refe.dateInscriptionBur


        Partager.open()
        cmd.ExecuteNonQuery()
        Partager.close()
        'remplirRef()
    End Sub

    'Public Function supprimer_Ref(ByVal Refe As Reference) As Integer
    '    Dim sc As Integer = 0
    '    Dim cmd As New SqlClient.SqlCommand("delete From Reference where idClt = " & CInt(Refe.idsClt.Item(0)) & " and Ref = " & Refe.Ref, Partager.con)
    '    Partager.open()
    '    cmd.ExecuteNonQuery()
    '    cmd.CommandText = "select count(*) from reference where Ref = " & CInt(Refe.Ref)
    '    sc = cmd.ExecuteScalar
    '    Partager.close()
    '    Return sc
    'End Function

    'Public Sub remplir_Ref()

    '    Me.List_Reference = New ArrayList
    '    Dim idRef As Integer
    '    Dim refe As Reference
    '    Partager.ad = New SqlClient.SqlDataAdapter("select * from Reference Order By Ref", Partager.conPhone)
    '    Partager.ds = New DataSet
    '    Partager.ad.Fill(Partager.ds, "Reference")

    '    idRef = -1

    '    For Each f As DataRow In Partager.ds.Tables(0).Rows

    '        If idRef <> CInt(f("Ref").ToString) Then
    '            idRef = CInt(f("Ref").ToString)
    '        Else
    '            Continue For
    '        End If

    '        refe = New Reference
    '        refe.Ref = idRef

    '        For Each r As DataRow In Partager.ds.Tables(0).Rows
    '            If idRef = CInt(r("Ref").ToString) Then
    '                refe.idsClt.Add(CInt(r(1).ToString))
    '            End If
    '        Next

    '        Me.List_Reference.Add(refe)

    '    Next
    '    Partager.close()
    'End Sub

    Public Sub remplirRef()
        Me.List_Reference = New List(Of Reference)
        Dim refe As Reference
        Dim cmd As New SqlClient.SqlCommand("select * from Reference", Partager.con)
        Partager.open()
        Partager.dr = cmd.ExecuteReader
        While Partager.dr.Read
            refe = New Reference(Partager.dr.GetInt32(0), Partager.dr.GetDateTime(1))
            Me.List_Reference.Add(refe)
        End While
        Partager.close()
        Partager.dr.Close()

        For Each refc In Me.List_Reference
            refc.remplir_lstRefAdv()
            refc.remplir_lstRefClt()
        Next
    End Sub

    Public Sub remplirRefAleger()
        Me.List_Reference = New List(Of Reference)
        Dim refe As Reference
        Dim cmd As New SqlClient.SqlCommand("select * from Reference", Partager.con)
        Partager.open()
        Partager.dr = cmd.ExecuteReader
        While Partager.dr.Read
            refe = New Reference(Partager.dr.GetInt32(0), Partager.dr.GetDateTime(1))
            Me.List_Reference.Add(refe)
        End While
        Partager.close()
        Partager.dr.Close()
    End Sub

    Public Sub supprimerRef(ByVal Refe As Reference)
        Dim cmd As New SqlClient.SqlCommand("delete From Reference where Ref = " & Refe.Ref, Partager.con)
        Partager.open()
        cmd.ExecuteNonQuery()
        Partager.close()
        'remplirRef()
    End Sub


    '------------------Les methode de la classe plainte----------------------
    Public Sub remplir_List_plainte()
        Partager.close()
        Me.List_Plainte.Clear()
        Dim cmd As New SqlClient.SqlCommand("select * from Plainte", Partager.con)
        Partager.open()
        Partager.dr = cmd.ExecuteReader
        Dim i As Integer = 0
        Dim nbrchamp As Integer = Partager.dr.FieldCount
        Dim plnt As Plainte
        While Partager.dr.Read
            listverif = New ArrayList
            For i = 0 To nbrchamp - 1
                If Partager.dr(i) Is DBNull.Value Then
                    listverif.Add(Nothing)
                Else
                    listverif.Add(Partager.dr(i))
                End If
            Next
            plnt = New Plainte(listverif)
            Me.List_Plainte.Add(plnt)
        End While
        Partager.dr.Close()
        Partager.close()
    End Sub

    Public Sub suprimmer_plainte(ByVal id As Integer)

        Dim cmd As New SqlClient.SqlCommand("delete from Plainte where id=" & id, Partager.con)

        Partager.open()
        cmd.ExecuteNonQuery()
        Partager.close()

        Me.remplir_List_plainte()
    End Sub

    Public Sub Ajouter_plainte(ByVal plnt As Plainte)

        Dim cmd As New SqlClient.SqlCommand("insert into Plainte values (@Num_Plainte,@Annee,@Type,'" & plnt.Date_Inscreption & "',@wakil,@Objecte,@Tribunal,@Observ,@Dicision,GETDATE())", Partager.con)

        cmd.Parameters.Add("@Num_Plainte", SqlDbType.Int).Value = plnt.Num_Plainte
        cmd.Parameters.Add("@Annee", SqlDbType.Int).Value = plnt.Annee
        cmd.Parameters.Add("@Type", SqlDbType.VarChar).Value = plnt.Type
        cmd.Parameters.Add("@wakil", SqlDbType.VarChar).Value = plnt.Nom_wakil
        cmd.Parameters.Add("@Objecte", SqlDbType.VarChar).Value = plnt.Objecte
        cmd.Parameters.Add("@Tribunal", SqlDbType.VarChar).Value = plnt.Tribunal
        cmd.Parameters.Add("@Observ", SqlDbType.Text).Value = plnt.Observ
        cmd.Parameters.Add("@Dicision", SqlDbType.VarChar).Value = plnt.dicision

        Partager.open()
        cmd.ExecuteNonQuery()
        Partager.close()

        Me.remplir_List_plainte()
    End Sub

    Public Sub modifier_plainte(ByVal plnt As Plainte, ByVal id As Integer)

        Dim cmd As New SqlClient.SqlCommand("update Plainte set  Num_Plainte=@Num_Plainte,Annee=@Annee,Type=@Type,Date_Inscreption='" & plnt.Date_Inscreption & "',Nom_wakil=@wakil,Object=@Object,Tribunal=@Tribunal,Observ=@Observ,Dicision=@Dicision where id=" & id, Partager.con)

        cmd.Parameters.Add("@Num_Plainte", SqlDbType.Int).Value = plnt.Num_Plainte
        cmd.Parameters.Add("@Annee", SqlDbType.Int).Value = plnt.Annee
        cmd.Parameters.Add("@Type", SqlDbType.VarChar).Value = plnt.Type
        cmd.Parameters.Add("@wakil", SqlDbType.VarChar).Value = plnt.Nom_wakil
        cmd.Parameters.Add("@Object", SqlDbType.VarChar).Value = plnt.Objecte
        cmd.Parameters.Add("@Tribunal", SqlDbType.VarChar).Value = plnt.Tribunal
        cmd.Parameters.Add("@Observ", SqlDbType.VarChar).Value = plnt.Observ
        cmd.Parameters.Add("@Dicision", SqlDbType.VarChar).Value = plnt.dicision

        Partager.open()
        cmd.ExecuteNonQuery()
        Partager.close()

        Me.remplir_List_plainte()

    End Sub
    '------------------Les methode de la classe Envoi----------------------
    Public Sub remplir_list_envoi()
        Dim dat As String = ""
        Me.list_envoi.Clear()
        Dim cmd As New SqlClient.SqlCommand("select * from Envois", Partager.con)
        Partager.open()
        Partager.dr = cmd.ExecuteReader
        While Partager.dr.Read
            If Partager.dr("Date_Envoi") = "01/01/1900" Then
                dat = ""
            Else
                dat = Partager.dr("Date_Envoi")
            End If
            Dim en As New Envoi(Partager.dr("Id"), Partager.dr("Num_Envoi"), dat, Partager.dr("Direction"), Partager.dr("Dicision"), Partager.dr("observ"))
            Me.list_envoi.Add(en)
        End While
        Partager.dr.Close()
        Partager.close()
    End Sub

    Public Sub suprimmer_envoi(ByVal id As Integer)

        Dim cmd As New SqlClient.SqlCommand("delete FROM  Envois where id=" & id, Partager.con)

        Partager.open()
        cmd.ExecuteNonQuery()
        Partager.close()

        Me.remplir_list_envoi()
    End Sub

    Public Sub Ajouter_envoi(ByVal en As Envoi)

        Dim cmd As New SqlClient.SqlCommand("insert into Envois values(@Num_Envoi,'" & en.Date_Envoi & "',@Direction,@Dicision,@observ,GETDATE())", Partager.con)

        cmd.Parameters.Add("@Num_Envoi", SqlDbType.VarChar).Value = en.Num_Envoi
        cmd.Parameters.Add("@Direction", SqlDbType.VarChar).Value = en.Direction
        cmd.Parameters.Add("@Dicision", SqlDbType.VarChar).Value = en.Dicision
        cmd.Parameters.Add("@observ", SqlDbType.VarChar).Value = en.observ
        Partager.open()
        cmd.ExecuteNonQuery()
        Partager.close()

        Me.remplir_list_envoi()
    End Sub

    Public Sub modifier_envoi(ByVal en As Envoi, ByVal id As Integer)

        Dim cmd As New SqlClient.SqlCommand("update Envois set  Num_Envoi=@Num_Envoi,Date_Envoi='" & en.Date_Envoi & "',Direction=@Direction,Dicision=@Dicision,observ=@observ where id=" & id, Partager.con)

        cmd.Parameters.Add("@Num_Envoi", SqlDbType.VarChar).Value = en.Num_Envoi
        cmd.Parameters.Add("@Direction", SqlDbType.VarChar).Value = en.Direction
        cmd.Parameters.Add("@Dicision", SqlDbType.VarChar).Value = en.Dicision
        cmd.Parameters.Add("@observ", SqlDbType.VarChar).Value = en.observ

        Partager.open()
        cmd.ExecuteNonQuery()
        Partager.close()

        Me.remplir_list_envoi()

    End Sub

    Public listProce As List(Of Proce)
    Public Sub remplirLstProce()
        Partager.close()
        Me.listProce.Clear()
        Dim cmd As New SqlClient.SqlCommand("select * from Proces", Partager.con)
        Partager.open()
        Partager.dr = cmd.ExecuteReader
        Dim i As Integer = 0
        Dim nbrchamp As Integer = Partager.dr.FieldCount
        Dim proc As Proce
        If Partager.dr.HasRows Then
            While Partager.dr.Read
                proc = New Proce(Partager.dr(1), Partager.dr(2), Partager.dr(3), Partager.dr(4), Partager.dr(5) _
                                 , Partager.dr(6), Partager.dr(9), Partager.dr(10), Partager.dr(11), , Partager.dr(0))

                For Each src In lstSource
                    If src.id = Partager.dr.GetInt32(7) Then
                        proc.src = src
                        Exit For
                    End If
                Next

                proc.ref.Ref = Partager.dr(8)
                Me.listProce.Add(proc)
            End While
        End If
        Partager.dr.Close()
        Partager.close()
    End Sub
    Public Sub AjouterProce(ByVal proc As Proce)
        Dim cmd As New SqlClient.SqlCommand( _
            "INSERT INTO [Proces]([numProce],[code],[annee],[tribunal],[dateProce],[sujet],[idSource],[idRef],[observation],[caracterClt],[caracterAdv],[dateAchevement])" _
     & " VALUES(@numProce,@code,@annee,@tribunal,@dateProce,@sujet,@idSource,@idRef,@observation,@caracterClt,@caracterAdv,GETDATE())", Partager.con)
        With proc
            cmd.Parameters.Add("@numProce", SqlDbType.Int).Value = .numProce
            cmd.Parameters.Add("@code", SqlDbType.Int).Value = .code
            cmd.Parameters.Add("@annee", SqlDbType.Int).Value = .annee
            cmd.Parameters.Add("@tribunal", SqlDbType.VarChar).Value = .tribunal
            cmd.Parameters.Add("@dateProce", SqlDbType.Date).Value = .dateProce
            cmd.Parameters.Add("@sujet", SqlDbType.VarChar).Value = .sujet
            cmd.Parameters.Add("@idSource", SqlDbType.Int).Value = .src.id
            cmd.Parameters.Add("@idRef", SqlDbType.Int).Value = .ref.Ref
            cmd.Parameters.Add("@observation", SqlDbType.VarChar).Value = .observation
            cmd.Parameters.Add("@caracterClt", SqlDbType.VarChar).Value = .caracClt
            cmd.Parameters.Add("@caracterAdv", SqlDbType.VarChar).Value = .caracAdv
        End With

        Partager.open()
        cmd.ExecuteNonQuery()
        Partager.close()

        Me.remplirLstProce()
    End Sub

    Public Sub supprimerProce(ByVal idPRoc As Integer)
        Dim cmd As New SqlClient.SqlCommand("DELETE FROM [Proces] WHERE idProce = " & idPRoc, Partager.con)

        Partager.open()
        cmd.ExecuteNonQuery()
        Partager.close()

        Me.remplirLstProce()
    End Sub

    Public Sub modifierProce(ByVal proc As Proce)

        Dim cmd As New SqlClient.SqlCommand("UPDATE [Proces] " & _
   "SET [numProce] = @numProce, " & _
      "[code] = @code, " & _
      "[annee] = @annee, " & _
      "[tribunal] = @tribunal, " & _
      "[dateProce] = @dateProce, " & _
      "[sujet] = @sujet, " & _
      "[idSource] = @idSource, " & _
      "[idRef] = @idRef, " & _
      "[observation] = @observation, " & _
      "[caracterClt] = @caracterClt, " & _
      "[caracterAdv] = @caracterAdv " & _
 "WHERE idProce = " & proc.idProce, Partager.con)

        With proc
            cmd.Parameters.Add("@numProce", SqlDbType.Int).Value = .numProce
            cmd.Parameters.Add("@code", SqlDbType.Int).Value = .code
            cmd.Parameters.Add("@annee", SqlDbType.Int).Value = .annee
            cmd.Parameters.Add("@tribunal", SqlDbType.VarChar).Value = .tribunal
            cmd.Parameters.Add("@dateProce", SqlDbType.Date).Value = .dateProce
            cmd.Parameters.Add("@sujet", SqlDbType.VarChar).Value = .sujet
            cmd.Parameters.Add("@idSource", SqlDbType.Int).Value = .src.id
            cmd.Parameters.Add("@idRef", SqlDbType.Int).Value = .ref.Ref
            cmd.Parameters.Add("@observation", SqlDbType.VarChar).Value = .observation
            cmd.Parameters.Add("@caracterClt", SqlDbType.VarChar).Value = .caracClt
            cmd.Parameters.Add("@caracterAdv", SqlDbType.VarChar).Value = .caracAdv
        End With

        Partager.open()
        cmd.ExecuteNonQuery()
        Partager.close()
        Me.remplirLstProce()
    End Sub

    'Méthodes Source
    Public lstSource As List(Of source)
    Public Sub remplirlstSource()
        Me.lstSource.Clear()
        Dim src As source
        Dim cmd As New SqlClient.SqlCommand("select * from Source", Partager.con)
        Partager.open()
        Partager.dr = cmd.ExecuteReader
        While Partager.dr.Read
            src = New source(Partager.dr.GetInt32(0), Partager.dr.GetString(1))
            Me.lstSource.Add(src)
        End While
        Partager.close()
        Partager.dr.Close()
    End Sub
    Public Sub ajouterSource(ByVal src As String)
        Try
            Dim cmd As New SqlClient.SqlCommand("INSERT INTO [Source]([Source]) " & _
    "VALUES(@Source)", Partager.con)
            cmd.Parameters.Add("@Source", SqlDbType.VarChar).Value = src

            Partager.open()
            cmd.ExecuteNonQuery()
            Partager.close()
            Me.remplirlstSource()
        Catch sqlExp As SqlException
            If sqlExp.Message.Contains("srcUnique") Then
                MsgBox("هذا المصدر يوجد من قبل", MsgBoxStyle.Critical, "خطأ")
            End If
        Catch ex As Exception

        End Try



    End Sub
    Public Sub supprimerSource(ByVal idSrc As Integer)
        Dim cmd As New SqlClient.SqlCommand("DELETE FROM [Source] WHERE id = " & idSrc, Partager.con)
        Partager.open()
        cmd.ExecuteNonQuery()
        Partager.close()
        Me.remplirlstSource()
    End Sub

End Class
